package com.wavefax.minbang.xu.learning.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class ExcelToJsonUtil {


    public void ExcelToJson(String filePath, Integer startSheet, Integer startRow) {

        String path = "D:/wavefax/jsons";//json文件的路径

        int flag = 2;//设置标志位：flag=0表示.xls文件,flag=1表示.xlsx文件


        if (startSheet == null) {//设置sheet和row初始值为0
            startSheet = 0;
        }
        if (startRow == null) {
            startRow = 0;
        }
        Workbook workbook = null;
        try {
            FileInputStream inputStream = new FileInputStream(filePath);//使用WorkbookFactory创建Workbook不必担心Excel2003或2007的版本问题
            if (filePath.endsWith(".xls")) {
                workbook = WorkbookFactory.create(inputStream);
                inputStream.close();
                flag = 0;
            } else if (filePath.endsWith(".xlsx")) {
                workbook = WorkbookFactory.create(inputStream);
                inputStream.close();
                flag = 1;
            }
        } catch (Exception e) {
            System.out.println("workbook对象创建失败");
        }

        Sheet sheet = workbook.getSheetAt(startSheet);//sheet工作表;row行

        Row row0 = sheet.getRow(startRow);//第startRow行，是表头部分
        Map<Integer, String> map = new HashMap<>();

        for (int i = 0; i < row0.getLastCellNum(); i++) {//将表头的值放进map里面，作为属性
            map.put(i, row0.getCell(i).toString());
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");//获取时间戳
        String timeStamp = sdf.format(new Date());
        try{
            OutputStream out = new FileOutputStream(new File(path,"xlsToJson"+timeStamp+".json"),true);
            System.out.println(sheet.getLastRowNum());
            StringBuilder jsonsb = new StringBuilder();
            byte[] bytes;
        for (int i = startRow + 1; i < sheet.getLastRowNum(); i++) {

            jsonsb.append("{");
            Row row = sheet.getRow(i);
            if ( row != null && map.size() == row.getLastCellNum() ) {//读取的行不能为空且读取的列数等于表头的列数
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    jsonsb.append("\""+map.get(j)+"\""+":\"");
                    jsonsb.append(row.getCell(j).toString()+"\"");
                    if(j!=row.getLastCellNum()-1){
                        jsonsb.append(",");
                    }
                }
                jsonsb.append("}\n");
                bytes = jsonsb.toString().getBytes();
                out.write(bytes);
                jsonsb.delete(0,jsonsb.length());//清除字符串
                out.flush();
            }
        }
                out.close();
        }catch (Exception e){
            System.out.println("输出到json文件失败");
        }


//        switch (flag){
//            case 0:{
//                try{
//                    OutputStream out = new FileOutputStream(new File(path,"xlsToJson"+timeStamp+".json"));
//                    String jsonString = jsons.toJSONString();
//                    byte[] bytes = jsonString.getBytes();
//                    out.write(bytes);
//                    out.flush();
//                    out.close();
//                    System.out.println("提取xls文件内容输出json文件成功");
//                }catch (Exception e){
//                    System.out.println("输出到json文件失败");
//                }
//                break;
//            }
//            case 1:{//输出到json文件
//                try{
//                    OutputStream out = new FileOutputStream(new File(path,"xlsxToJson"+timeStamp+".json"));
//                    String jsonString = jsons.toJSONString();
//                    byte[] bytes = jsonString.getBytes();
//                    out.write(bytes);
//                    out.flush();
//                    out.close();
//                }catch (Exception e){
//                    System.out.println("输出到json文件失败");
//                }
//                break;
//            }
//            default:
//        }
    }
}
